﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.MODEL;
using System.IO;
using PdfSharp.Pdf;
using KPIS.GERP.GAMS.APP.App_UI.Commons;
using PdfSharp.Pdf.AcroForms;
using System.Diagnostics;
using PdfSharp.Drawing;
using PdfSharp.Pdf.IO;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class RdsPopup : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        D_ACM_Finance_Payment ACMPAY = new D_ACM_Finance_Payment();
        D_ACM_Finance_Recive ACMRECV = new D_ACM_Finance_Recive();
        static string path;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string type = Request.QueryString["type"];
                string month = Request.QueryString["month"];
                string year = Request.QueryString["year"];
                string source = Request.QueryString["source"];

                if (String.IsNullOrEmpty(type)
                    || String.IsNullOrEmpty(month)
                    || String.IsNullOrEmpty(year)
                    || String.IsNullOrEmpty(source))
                {
                    main.MessageAlert(Page, "มีข้อผิดพลาดในการออกรายงาน");
                    return;
                }
                    

                path = Server.MapPath("~/App_Data/FileTemplate");
                CheckFolderExist(path);

                string filetype = string.Empty;
                if (type.StartsWith("alt"))
                    filetype = "xlsx";
                else
                    filetype = "pdf";

                if (type.Equals("pp30"))
                    GetPP30(month, year, source);
                else if (type.Contains("pnd3"))
                    GetPND(month, year, source, main.ConvertNullInt(contact_type.Person), filetype);
                else if (type.Contains("pnd53"))
                    GetPND(month, year, source, main.ConvertNullInt(contact_type.JuristicPerson), filetype);
                else
                    return;

            }
        }

        private void GetPND(string month, string year, string source, int? type, string filetype)
        {
            string YearEn = (Convert.ToInt32(year) - 543).ToString();
            IList<M_ACM_Finance_Payment> lstPND = ACMPAY.GetvalueAccPND(main.ConvertNullInt(month), main.ConvertNullInt(YearEn), main.ConvertNullInt(source), type);

            string filename = string.Empty; string stype = string.Empty;
            if (type == main.ConvertNullInt(contact_type.Person))
            {
                stype = "pnd3." + filetype;
                filename = Path.Combine(path, stype);
            }
            else if (type == main.ConvertNullInt(contact_type.JuristicPerson))
            {
                stype = "pnd53." + filetype;
                filename = Path.Combine(path, stype);
            }
            else
                return;

            if (!File.Exists(filename))
                return;

            if (filetype.Equals("pdf"))
                PdfFile(month, year, lstPND, filename, stype);
            else if (filetype.Equals("xlsx"))
                ExcelFile(lstPND, stype, filename);
        }

        private void ExcelFile(IList<M_ACM_Finance_Payment> lstPND, string stype, string filename)
        {
            if (lstPND.Count <= 0) return;
            int rows = 7; //fix start row
            Dictionary<string, string> _dtD = GetData(lstPND, stype, ref rows);

            Dictionary<string, string> _dtH = new Dictionary<string, string>();
            double? tax = lstPND.Sum(o => o.tax);
            double? amt = lstPND.Sum(o => o.amount) + tax;            
            //_dtH.Add("1:1", lstPND.Select(s => s.pay_date.Value).FirstOrDefault().ToString("MMMM yyyy", new System.Globalization.CultureInfo("th-TH")));
            _dtH.Add("1:1", amt.Value.ToString("#,##0.00"));
            _dtH.Add("1:2", tax.Value.ToString("#,##0.00"));


            WriteDicToExcel(new Dictionary<string, string>(), _dtH, _dtD, stype, filename, ref rows, lstPND);
        }

        private void PdfFile(string month, string year, IList<M_ACM_Finance_Payment> lstPND, string filename, string stype)
        {
            if (lstPND.Count <= 0)
            {
                //Response.Write("   Not Found Data!");
                //return;
            }
            double? tax = lstPND.Sum(o => o.tax);
            double? amt = lstPND.Sum(o => o.amount) + tax;
            double cntPages = Math.Ceiling((((double)lstPND.Count + 3) / 15.0));

            #region[PdfDocument]
            //Open document with convert compatible
            PdfDocument document = PdfReader.Open(filename, PdfDocumentOpenMode.Modify);//PdfReaderCompatible.Open(filename);

            PdfAcroForm form = document.AcroForm;
            // Get all form fields of the whole document
            PdfAcroField.PdfAcroFieldCollection fields = form.Fields;
            double fontSize = 16;
            string fontFam = "TH SarabunPSK";
            XFont myfont = new XFont(fontFam, fontSize, XFontStyle.Regular, new XPdfFontOptions(PdfFontEncoding.Unicode));

            PdfTextField txtYear = (PdfTextField)fields["txtYear"];
            txtYear.Font = myfont;
            PdfString strTmp = new PdfString(year);
            txtYear.Text = year;
            txtYear.Value = strTmp;

            PdfCheckBoxField c = (PdfCheckBoxField)fields["ckbMonth_" + month];
            c.Checked = true;

            PdfTextField txtAttachPerson = (PdfTextField)fields["txtAttachPerson"];
            txtAttachPerson.Font = myfont;
            strTmp = new PdfString(lstPND.Count.ToString("#,##0"));
            txtAttachPerson.Text = lstPND.Count.ToString("#,##0");
            txtAttachPerson.Value = strTmp;

            PdfTextField txtAttachSheet = (PdfTextField)fields["txtAttachSheet"];
            txtAttachSheet.Font = myfont;
            strTmp = new PdfString(cntPages.ToString("#,##0"));
            txtAttachSheet.Text = cntPages.ToString("#,##0");
            txtAttachSheet.Value = strTmp;

            PdfTextField txtSumTax1 = (PdfTextField)fields["txtSumTax1"];
            txtSumTax1.Font = myfont;
            strTmp = new PdfString(amt.Value.ToString("#,##0.00"));
            txtSumTax1.Text = amt.Value.ToString("#,##0.00");
            txtSumTax1.Value = strTmp;

            PdfTextField txtSumTax2 = (PdfTextField)fields["txtSumTax2"];
            txtSumTax2.Font = myfont;
            strTmp = new PdfString(tax.Value.ToString("#,##0.00"));
            txtSumTax2.Text = tax.Value.ToString("#,##0.00");
            txtSumTax2.Value = strTmp;

            PdfTextField txtSumTax4 = (PdfTextField)fields["txtSumTax4"];
            txtSumTax4.Font = myfont;
            strTmp = new PdfString(tax.Value.ToString("#,##0.00"));
            txtSumTax4.Text = tax.Value.ToString("#,##0.00");
            txtSumTax4.Value = strTmp;

            if (Session["UserName"] != null)
            {
                PdfTextField txts = (PdfTextField)fields["txtSignature"];
                strTmp = new PdfString(Session["UserName"].ToString());
                txts.Font = myfont;
                txts.Text = Session["UserName"].ToString();
                txts.Value = strTmp;
            }

            if (Session["PosName"] != null)
            {
                PdfTextField txtPosition = (PdfTextField)fields["txtPosition"];
                strTmp = new PdfString(Session["PosName"].ToString());
                txtPosition.Font = myfont;
                txtPosition.Text = Session["PosName"].ToString();
                txtPosition.Value = strTmp;
            }

            string dt = DateTime.Now.ToString("d/MMMM/yyyy", new System.Globalization.CultureInfo("th-TH"));
            string[] arrDt = dt.Split('/');
            if (arrDt.Length >= 3)
            {
                PdfTextField txtDay = (PdfTextField)fields["txtDay"];
                strTmp = new PdfString(arrDt[0].ToString());
                txtDay.Font = myfont;
                txtDay.Text = arrDt[0].ToString();
                txtDay.Value = strTmp;

                PdfTextField txtMonth = (PdfTextField)fields["txtMonth"];
                strTmp = new PdfString(arrDt[1].ToString());
                txtMonth.Font = myfont;
                txtMonth.Text = arrDt[1].ToString();
                txtMonth.Value = strTmp;
            }

            PdfTextField txtYear2 = (PdfTextField)fields["txtYear2"];
            txtYear2.Font = myfont;
            strTmp = new PdfString(year);
            txtYear2.Text = year;
            txtYear2.Value = strTmp;
            #endregion[PdfDocument]

            #region[stream pdf]
            string newfilename = stype.Split('.').ElementAt(0) + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".pdf";

            MemoryStream stream = new System.IO.MemoryStream();
            document.Save(stream, false);
            stream.Position = 0;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-length", stream.Length.ToString());
            Response.AddHeader("content-disposition", "inline;filename=" + newfilename);
            Response.BinaryWrite(stream.ToArray());

            stream.Close();
            Response.Flush();
            Response.End();
            #endregion[stream pdf]
        }

        private void GetPP30(string cmbMonthly, string cmbYear, string source)
        {
            string YearEn = (Convert.ToInt32(cmbYear) - 543).ToString();
            Dictionary<string, decimal> ret = ACMPAY.GetvalueAccPP(cmbMonthly, YearEn, source);
            System.Data.DataTable dtPay = ACMPAY.ReportTaxPay(int.Parse(cmbMonthly), int.Parse(YearEn) + 543);
            System.Data.DataTable dtRecv = ACMRECV.ReportTaxSale(int.Parse(cmbMonthly), int.Parse(YearEn) + 543);
            double vatBuy = 0;
            double amountBuy = 0;
            double vatSale = 0;
            double amountSale = 0;
            double exceptvat = 0;


            foreach (System.Data.DataRow r in dtPay.Rows)
            {
                vatBuy += double.Parse(r["Tax"].ToString());
                amountBuy += double.Parse(r["amountIncludetax"].ToString());                
            }

            foreach (System.Data.DataRow r2 in dtRecv.Rows)
            {
                vatSale += double.Parse(r2["vatamount"].ToString());
                amountSale += double.Parse(r2["IncludeVat"].ToString());
                exceptvat += double.Parse(r2["a"].ToString());
            }

            if (ret.Count <= 0)
            {
                //Response.Write("   Not Found Data!");
                //return;
            }

            //******PdfSharp******//
            string filename = Path.Combine(path, "pp30.pdf");
            if (!File.Exists(filename))
                return;
            //Open document with convert compatible
            PdfDocument document = PdfReader.Open(filename, PdfDocumentOpenMode.Modify);//PdfReaderCompatible.Open(filename);

            PdfAcroForm form = document.AcroForm;
            // Get all form fields of the whole document
            PdfAcroField.PdfAcroFieldCollection fields = form.Fields;
            double fontSize = 16;
            string fontFam = "TH SarabunPSK";
            XFont myfont = new XFont(fontFam, fontSize, XFontStyle.Regular, new XPdfFontOptions(PdfFontEncoding.Unicode));

            PdfTextField txt = (PdfTextField)fields["txtYear"];
            txt.Font = myfont;            
            PdfString strTmp = new PdfString(cmbYear);
            txt.Text = cmbYear;
            txt.Value = strTmp;
            PdfCheckBoxField c = (PdfCheckBoxField)fields["ckbMonth_" + cmbMonthly];
            c.Checked = true;

            double value = 0;
            PdfTextField txt1 = (PdfTextField)fields["txt1"];
            if (amountSale > 0) value = amountSale + exceptvat;
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt1.Font = myfont;
            txt1.Text = value.ToString("#,##0.00");
            txt1.Value = strTmp;

            value = 0;
            PdfTextField txt2 = (PdfTextField)fields["txt2"];
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt2.Font = myfont;
            txt2.Text = value.ToString("#,##0.00");
            txt2.Value = strTmp;

            double saleExclueVat = 0;
            PdfTextField txt3 = (PdfTextField)fields["txt3"];
            if (exceptvat > 0) saleExclueVat = exceptvat;
            strTmp = new PdfString(saleExclueVat.ToString("#,##0.00"));
            txt3.Font = myfont;
            txt3.Text = saleExclueVat.ToString("#,##0.00");
            txt3.Value = strTmp;

            double sumSaleAmount = 0;
            PdfTextField txt4 = (PdfTextField)fields["txt4"];
            if (amountSale > 0) sumSaleAmount = amountSale;
            strTmp = new PdfString(sumSaleAmount.ToString("#,##0.00"));
            txt4.Font = myfont;
            txt4.Text = sumSaleAmount.ToString("#,##0.00");
            txt4.Value = strTmp;

            double taxSale = 0;
            PdfTextField txt5 = (PdfTextField)fields["txt5"];
            if (vatSale > 0) taxSale = vatSale;
            strTmp = new PdfString(taxSale.ToString("#,##0.00"));
            txt5.Font = myfont;
            txt5.Text = taxSale.ToString("#,##0.00");
            txt5.Value = strTmp;

            double sumPayAmount = 0;
            PdfTextField txt6 = (PdfTextField)fields["txt6"];
            if (amountBuy > 0) sumPayAmount = amountBuy;
            strTmp = new PdfString(sumPayAmount.ToString("#,##0.00"));
            txt6.Font = myfont;
            txt6.Text = sumPayAmount.ToString("#,##0.00");
            txt6.Value = strTmp;

            double taxBuy = 0;
            PdfTextField txt7 = (PdfTextField)fields["txt7"];
            if (vatBuy > 0) taxBuy = vatBuy;
            strTmp = new PdfString(taxBuy.ToString("#,##0.00"));
            txt7.Font = myfont;
            txt7.Text = taxBuy.ToString("#,##0.00");
            txt7.Value = strTmp;

            double val8 = 0; double val9 = 0;

            PdfTextField txt8 = (PdfTextField)fields["txt8"];
            txt8.Font = myfont;
            txt8.Text = val8.ToString("#,##0.00");
            if (taxSale > taxBuy)
            {
                val8 = taxSale - taxBuy;               
                strTmp = new PdfString(val8.ToString("#,##0.00"));                
                txt8.Text = val8.ToString("#,##0.00");
                txt8.Value = strTmp;
            }

            PdfTextField txt9 = (PdfTextField)fields["txt9"];
            txt9.Font = myfont;
            strTmp = new PdfString(val9.ToString("#,##0.00"));
            if (taxSale < taxBuy)
            {
                val9 = taxBuy - taxSale;                
                strTmp = new PdfString(val9.ToString("#,##0.00"));                
                txt9.Text = val9.ToString("#,##0.00");
                txt9.Value = strTmp;
            }

            double val10 = 0;
            PdfTextField txt10 = (PdfTextField)fields["txt10"];
            if (ret.ContainsKey("amountCr")) taxBuy = double.Parse(ret["amountCr"].ToString());
            strTmp = new PdfString(val10.ToString("#,##0.00"));
            txt10.Font = myfont;
            txt10.Text = val10.ToString("#,##0.00");
            txt10.Value = strTmp;

            //decimal amountDr = 0;
            //if (ret.ContainsKey("amountDr")) amountDr = ret["amountDr"];

            double val11 = 0; double val12 = 0;

            if (val8 > val10)
            {
                PdfCheckBoxField c11 = (PdfCheckBoxField)fields["ckb11"];
                c11.Checked = true;
                val11 = double.Parse(txt8.Value.ToString()) - double.Parse(txt10.Value.ToString());
                PdfTextField txt11 = (PdfTextField)fields["txt11"];
                strTmp = new PdfString(val11.ToString("#,##0.00"));
                txt11.Font = myfont;
                txt11.Text = val11.ToString("#,##0.00");
                txt11.Value = strTmp;

                PdfTextField txt12 = (PdfTextField)fields["txt12"];
                strTmp = new PdfString((0).ToString("#,##0.00"));
                txt12.Font = myfont;
                txt12.Text = (0).ToString("#,##0.00");
                txt12.Value = strTmp;
            }

            if ((val8 < val10) || (val8 < val9 + val10))
            {
                PdfTextField txt11 = (PdfTextField)fields["txt11"];
                strTmp = new PdfString((0).ToString("#,##0.00"));
                txt11.Font = myfont;
                txt11.Text = (0).ToString("#,##0.00");
                txt11.Value = strTmp;

                PdfCheckBoxField c12 = (PdfCheckBoxField)fields["ckb12"];
                c12.Checked = true;

                val12 = val9 + val10;
                PdfTextField txt12 = (PdfTextField)fields["txt12"];
                strTmp = new PdfString(val10.ToString("#,##0.00"));
                txt12.Font = myfont;
                txt12.Text = val10.ToString("#,##0.00");
                txt12.Value = strTmp;
            }

            decimal val13 = 0;
            PdfTextField txt13 = (PdfTextField)fields["txt13"];
            txt13.Font = myfont;
            txt13.Text = val13.ToString("#,##0.00");

            decimal val14 = 0;
            PdfTextField txt14 = (PdfTextField)fields["txt14"];
            txt14.Font = myfont;
            txt14.Text = val14.ToString("#,##0.00");


            PdfTextField txt15 = (PdfTextField)fields["txt15"];
            strTmp = new PdfString(val11.ToString("#,##0.00"));
            txt15.Font = myfont;
            txt15.Text = val11.ToString("#,##0.00");
            txt15.Value = strTmp;

            PdfTextField txt16 = (PdfTextField)fields["txt16"];
            strTmp = new PdfString(val12.ToString("#,##0.00"));
            txt16.Font = myfont;
            txt16.Text = val12.ToString("#,##0.00");
            txt16.Value = strTmp;


            if (Session["UserName"] != null)
            {
                PdfTextField txts = (PdfTextField)fields["txtSignature"];
                strTmp = new PdfString(Session["UserName"].ToString());
                txts.Font = myfont;
                txts.Text = Session["UserName"].ToString();
                txts.Value = strTmp;
            }

            string dt = DateTime.Now.ToString("d MMMM yyyy", new System.Globalization.CultureInfo("th-TH"));
            PdfTextField txtd = (PdfTextField)fields["txtDate"];
            strTmp = new PdfString(dt);
            txtd.Font = myfont;
            txtd.Text = dt;
            txtd.Value = strTmp;

            //filename = filename.Substring(0, filename.Length - 4);
            //filename = filename + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".pdf";

            MemoryStream stream = new System.IO.MemoryStream();
            document.Save(stream, false);
            stream.Position = 0;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-length", stream.Length.ToString());
            Response.BinaryWrite(stream.ToArray());
            Response.Flush();
            stream.Close();
            Response.End();

        }

        private void CheckFolderExist(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
                main.FolderACL("Everyone", path);
            }
        }

        #region[Excel]

        private Dictionary<string, string> GetData(IList<M_ACM_Finance_Payment> lstModel, string stype, ref int rows)
        {
            #region[PND]
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (stype.ToUpper().StartsWith("PND"))
            {
                dt = new Dictionary<string, string>();
                foreach (M_ACM_Finance_Payment vModel in lstModel)
                {
                    if ((vModel.tax ?? 0) >= 0)
                    {
                        n += 1;// seq
                        rows += 1;//start with row 8
                        //double? amt = vModel.amount + vModel.tax;
                        double? amt = vModel.pr_amount - vModel.vat;
                        string bgmProjOperCode = ACMPAY.getBgmOperaProjCode(vModel.money_req_seq.ToString());

                        dt.Add(rows.ToString() + ":" + (1).ToString(), n.ToString());
                        dt.Add(rows.ToString() + ":" + (3).ToString(), !String.IsNullOrEmpty(vModel.contact_name) ? vModel.contact_name : "");

                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.contact_person_name);
                        //////dt.Add(rows.ToString() + ":" + (5).ToString(), vModel.ref_no);
                        dt.Add(rows.ToString() + ":" + (33).ToString(), vModel.pay_date.HasValue ?
                            vModel.pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                        //Modified show column change from PayName to RefNo
                        //dt.Add(rows.ToString() + ":" + (37).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                        dt.Add(rows.ToString() + ":" + (37).ToString(), !String.IsNullOrEmpty(bgmProjOperCode) ? bgmProjOperCode : "");
                        dt.Add(rows.ToString() + ":" + (47).ToString(), (vModel.tax_rate.HasValue ?
                            vModel.tax_rate.Value.ToString("#,##0.00") : "0.00"));
                        dt.Add(rows.ToString() + ":" + (51).ToString(), amt.Value.ToString("#,##0.00"));
                        dt.Add(rows.ToString() + ":" + (58).ToString(), vModel.tax.HasValue ?
                            vModel.tax.Value.ToString("#,##0.00") : "0.00");
                        rows += 1;
                        dt.Add(rows.ToString() + ":" + (3).ToString(), vModel.remarks);
                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.ref_no);
                    }
                }
            }
            return dt;
            #endregion[PND]
        }

        private void WriteDicToExcel(Dictionary<string, string> _dtM, Dictionary<string, string> _dtH
            , Dictionary<string, string> _dtD, string stype, string filename, ref int rows, IList<M_ACM_Finance_Payment> lstPND)
        {
            if (_dtD.Count <= 0) return;

            FileInfo newFile = new FileInfo(filename);
            if (!newFile.Exists) return;

            #region[ExcelPackage]
            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                //if (_dtM.Count > 0)
                //    AddWorkSheet(_dtM, "M", ':', xlPackage);

                //if (_dtH.Count > 0)
                //    AddWorkSheet(_dtH, "H", ':', xlPackage);

                if (_dtD.Count > 0)
                    AddWorkSheet(_dtD, "Application Form2", ':', xlPackage, rows, lstPND);

                WriteFile(stype.Split('.').ElementAt(0), xlPackage);
            }
            #endregion[ExcelPackage]
        }

        private static void AddWorkSheet(Dictionary<string, string> _dtM, string _sheet, char _char, ExcelPackage xlPackage, int rows, IList<M_ACM_Finance_Payment> lstPND)
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[_sheet];
            if (worksheet != null)
            {
                bool IsFooter = true;
                #region[Render Detial]
                foreach (KeyValuePair<string, string> item in _dtM)
                {
                    bool IsMerge = true;
                    
                    string[] _cell = item.Key.Split(_char);
                    #region [previous code]
                    //if (_cell.Length >= 2)
                    //{
                    //    int row = Convert.ToInt32(_cell[0]);
                    //    int col = Convert.ToInt32(_cell[1]);
                    //    //ชื่อ ที่อยู่ = col3-col24, เลขที่ประจำตัวผู้เสียภาษี = col26-col31,  วันเดือนปี = col33-col35,
                    //    //ประเภทเงินได้ = col37-col46, อัตราภาษี = col47-49, จำนวนเงินที่จ่าย = col51-col54, จำนวนเงินที่หัก = col58-col60
                    //    worksheet.Cells[row, col].Value = item.Value;
                    //    worksheet.Cells[row, col].Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                    //    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    //}
                    #endregion [previous code]
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);                       
                        worksheet.Cells[row, col].Value = item.Value;
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                        if (IsMerge)
                        {
                            //ชื่อ ที่อยู่ = col3-col24, เลขที่ประจำตัวผู้เสียภาษี = col26-col31,  วันเดือนปี = col33-col35,
                            //ประเภทเงินได้ = col37-col46, อัตราภาษี = col47-49, จำนวนเงินที่จ่าย = col51-col54, จำนวนเงินที่หัก = col58-col60
                            switch (col)
                            {
                                case 3: worksheet.Cells[row, col, row, col + 21].Merge = true; break;
                                case 26: worksheet.Cells[row, col, row, col + 5].Merge = true; break;
                                case 33: worksheet.Cells[row, col, row, col + 2].Merge = true; break;
                                case 37: worksheet.Cells[row, col, row + 1, col + 9].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                    worksheet.Cells[row, col].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
                                    worksheet.Cells[row, col].Style.WrapText = true;
                                    break;
                                case 47: worksheet.Cells[row, col, row, col + 2].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                                case 51: worksheet.Cells[row, col, row, col + 3].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                                case 58: worksheet.Cells[row, col, row, col + 2].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                            }
                        }
                    }
                }
                #endregion[Render Detial]

                #region[Render Footer]
                if (IsFooter)
                {
                    worksheet.Cells["A7:BN7"].Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
                    worksheet.Cells["A" + rows.ToString() + ":BN" + rows.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;

                    string message = "รวมยอดเงินที่ได้และภาษีที่นำส่ง(นำไปรวมกับใบแนบฉบับอื่น(ถ้ามี))";
                    worksheet.Cells["AE" + (rows + 1).ToString() + ":AT" + (rows + 1).ToString()].Value = "รวมยอดเงินที่ได้และภาษีที่นำส่ง(นำไปรวมกับใบแนบฉบับอื่น(ถ้ามี))";
                    using (ExcelRange r = worksheet.Cells["AE" + (rows + 1).ToString() + ":AT" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                    //Modified on 04/06/57
                    double? tax = lstPND.Sum(o => o.tax);
                    double? amt = lstPND.Sum(o => o.amount) + tax;
                    //double? tax = lstPND.Where(x => x.tax_rate != 0).Sum(x => x.tax);
                    //double? amt = lstPND.Where(x => x.tax_rate != 0).Sum(x => (x.pr_amount - x.vat));
                    worksheet.Cells["AY" + (rows + 1).ToString() + ":BB" + (rows + 1).ToString()].Value = amt.Value.ToString("#,##0.00");
                    using (ExcelRange r = worksheet.Cells["AY" + (rows + 1).ToString() + ":BB" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }

                    worksheet.Cells["BF" + (rows + 1).ToString() + ":BH" + (rows + 1).ToString()].Value = tax.Value.ToString("#,##0.00");
                    using (ExcelRange r = worksheet.Cells["BF" + (rows + 1).ToString() + ":BH" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }

                    worksheet.Cells["A" + (rows + 2).ToString() + ":BN" + (rows + 2).ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Double;

                    message = "(ให้กรอกลำดับที่ต่อเนื่องกันไปทุกแผ่น)";
                    worksheet.Cells["A" + (rows + 3).ToString() + ":J" + (rows + 3).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 3).ToString() + ":J" + (rows + 3).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "(1)ให้ระบุว่าจ่ายเป็นค่าอะไร เช่น ค่านายหน้า ค่าแห่งกู๊ดวิลล์ ดอกเบี้ยเงินฝาก ดอกเบี้ย ตั๋วเงิน เงินปันผล เงินส่วนแบ่งกำไร ค่าเช่าอาคาร";
                    message += "ค่าสอบบัญชี ค่าออกแบบ ค่าก่อสร้างโรงเรียน ค่าซื้อเครื่องพิมพ์ดีด ค่าซื้อพืชผลทางการเกษตร (ยางพารา มันสำปะหลัง ปอ ข้าว ฯลฯ)";
                    message += "ค่าจ้างทำของ ค่าจ้างโฆษณา รางวัล ส่วนลดหรือประโยชน์ใดๆ เนื่องจากการส่งเสริมการขาย รางวัลในการประกวด การแข่งขัน";
                    message += "การชิงโชค ค่าขนส่งสินค้า ค่าเบี้ยประกันวินาศภัย";

                    worksheet.Cells["A" + (rows + 4).ToString() + ":AE" + (rows + 7).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 4).ToString() + ":AE" + (rows + 7).ToString()])
                    {
                        r.Merge = true;
                        r.Style.WrapText = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "(2)เงื่อนไขการหักภาษี ณ ที่จ่าย ให้กรอกดังนี้   หัก ณ ที่จ่าย กรอก 1  ออกภาษีให้ กรอก 2";
                    worksheet.Cells["A" + (rows + 8).ToString() + ":AE" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 8).ToString() + ":AE" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "ประทับตรานิติบุคคล(ถ้ามี)";
                    worksheet.Cells["AG" + (rows + 4).ToString() + ":AK" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AG" + (rows + 4).ToString() + ":AK" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.WrapText = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        r.Style.Border.Left.Style = ExcelBorderStyle.DashDot;
                        r.Style.Border.Right.Style = ExcelBorderStyle.DashDot;
                    }

                    message = "ลงชื่อ...............................................................ผู้จ่ายเงิน";
                    worksheet.Cells["AO" + (rows + 5).ToString() + ":BD" + (rows + 5).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 5).ToString() + ":BD" + (rows + 5).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = "(...................................................................)";
                    worksheet.Cells["AO" + (rows + 6).ToString() + ":BD" + (rows + 6).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 6).ToString() + ":BD" + (rows + 6).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = "ตำแหน่ง_____________________________";
                    worksheet.Cells["AO" + (rows + 7).ToString() + ":BD" + (rows + 7).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 7).ToString() + ":BD" + (rows + 7).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = DateTime.Now.ToString("dd MMMM yyyy", new System.Globalization.CultureInfo("th-TH"));
                    worksheet.Cells["AO" + (rows + 8).ToString() + ":BD" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 8).ToString() + ":BD" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }
                }
                #endregion[Render Footer]
            }
        }

        private void WriteFile(string stype, ExcelPackage xlPackage)
        {
            string filename = stype + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            Response.Clear();
            Response.ClearContent();
            //Response.ClearHeaders();
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(xlPackage.GetAsByteArray());
            Response.Flush();

            Response.End();
        }
        #endregion[Excel]
    }
}